


--use PolePosition
/*
-- // checksum failed custom error
exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO
*/


--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_Utility_Asset_Transformers') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_Utility_Asset_Transformers
GO

create view v_tb_Utility_Asset_Transformers as
	/* 	Initial Generated Date:  Sunday, June 13, 2010 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [Utility_Asset_TransformerId] as [Utility_Asset_TransformerId]
	,[UtilityFK] as [UtilityFK]
	,[Vendor_ProjectFK] as [Vendor_ProjectFK]
	,[Latitude] as [Latitude]
	,[Longitude] as [Longitude]
	,[CreatedDate] as [CreatedDate]
	,[SerialNumber] as [SerialNumber]
	,[Comments] as [Comments]
	,[ImageFileName] as [ImageFileName]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [ImageFileName]), 255) as [FriendlyName]
from
	tb_Utility_Asset_Transformers
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetTransformer_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetTransformer_Test
GO


create proc up_UtilityAssetTransformer_Test
	@Utility_Asset_TransformerId int
as
	/* 	Initial Generated Date:  Sunday, June 13, 2010 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [Utility_Asset_TransformerId] 
	from [tb_Utility_Asset_Transformers] source
	where [source].[Utility_Asset_TransformerId] = @Utility_Asset_TransformerId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetTransformer_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetTransformer_Select
GO

create proc up_UtilityAssetTransformer_Select
	 @Utility_Asset_TransformerId int = null 
	,@UtilityFK int = null 
	,@Vendor_ProjectFK int = null 
	,@Latitude float = null 
	,@Longitude float = null 
	,@CreatedDate datetime = null 
	,@SerialNumber varchar(25) = null 
	,@Comments ntext = null 
	,@ImageFileName nvarchar(250) = null 
as
	/* 	Initial Generated Date:  Sunday, June 13, 2010 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select * from v_tb_Utility_Asset_Transformers
where
		IsNull([v_tb_Utility_Asset_Transformers].[Utility_Asset_TransformerId], -1) = Coalesce(@Utility_Asset_TransformerId, [v_tb_Utility_Asset_Transformers].[Utility_Asset_TransformerId], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[UtilityFK], -1) = Coalesce(@UtilityFK, [v_tb_Utility_Asset_Transformers].[UtilityFK], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[Vendor_ProjectFK], -1) = Coalesce(@Vendor_ProjectFK, [v_tb_Utility_Asset_Transformers].[Vendor_ProjectFK], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[Latitude], -1) = Coalesce(@Latitude, [v_tb_Utility_Asset_Transformers].[Latitude], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[Longitude], -1) = Coalesce(@Longitude, [v_tb_Utility_Asset_Transformers].[Longitude], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[CreatedDate], '1/1/1900 12:00AM') = Coalesce(@CreatedDate, [v_tb_Utility_Asset_Transformers].[CreatedDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Utility_Asset_Transformers].[SerialNumber], '') = Coalesce(@SerialNumber, [v_tb_Utility_Asset_Transformers].[SerialNumber], '')
	AND	IsNull(Convert(varchar(2000), [v_tb_Utility_Asset_Transformers].[Comments]), '') = Coalesce(Convert(varchar(2000), @Comments), Convert(varchar(2000), [v_tb_Utility_Asset_Transformers].[Comments]), '')
	AND	IsNull([v_tb_Utility_Asset_Transformers].[ImageFileName], '') = Coalesce(@ImageFileName, [v_tb_Utility_Asset_Transformers].[ImageFileName], '')
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetTransformer_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetTransformer_Select_Top1
GO

create proc up_UtilityAssetTransformer_Select_Top1
	 @Utility_Asset_TransformerId int = null 
	,@UtilityFK int = null 
	,@Vendor_ProjectFK int = null 
	,@Latitude float = null 
	,@Longitude float = null 
	,@CreatedDate datetime = null 
	,@SerialNumber varchar(25) = null 
	,@Comments ntext = null 
	,@ImageFileName nvarchar(250) = null 
as
	/* 	Initial Generated Date:  Sunday, June 13, 2010 
		Purpose: Selected top 1 record based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select TOP 1 * from v_tb_Utility_Asset_Transformers
where
		IsNull([v_tb_Utility_Asset_Transformers].[Utility_Asset_TransformerId], -1) = Coalesce(@Utility_Asset_TransformerId, [v_tb_Utility_Asset_Transformers].[Utility_Asset_TransformerId], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[UtilityFK], -1) = Coalesce(@UtilityFK, [v_tb_Utility_Asset_Transformers].[UtilityFK], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[Vendor_ProjectFK], -1) = Coalesce(@Vendor_ProjectFK, [v_tb_Utility_Asset_Transformers].[Vendor_ProjectFK], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[Latitude], -1) = Coalesce(@Latitude, [v_tb_Utility_Asset_Transformers].[Latitude], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[Longitude], -1) = Coalesce(@Longitude, [v_tb_Utility_Asset_Transformers].[Longitude], -1)
	AND	IsNull([v_tb_Utility_Asset_Transformers].[CreatedDate], '1/1/1900 12:00AM') = Coalesce(@CreatedDate, [v_tb_Utility_Asset_Transformers].[CreatedDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Utility_Asset_Transformers].[SerialNumber], '') = Coalesce(@SerialNumber, [v_tb_Utility_Asset_Transformers].[SerialNumber], '')
	AND	IsNull(Convert(varchar(2000), [v_tb_Utility_Asset_Transformers].[Comments]), '') = Coalesce(Convert(varchar(2000), @Comments), Convert(varchar(2000), [v_tb_Utility_Asset_Transformers].[Comments]), '')
	AND	IsNull([v_tb_Utility_Asset_Transformers].[ImageFileName], '') = Coalesce(@ImageFileName, [v_tb_Utility_Asset_Transformers].[ImageFileName], '')
GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetTransformer_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetTransformer_Insert
GO

create proc up_UtilityAssetTransformer_Insert
	 @Utility_Asset_TransformerId int =null OUTPUT  
	,@UtilityFK int  
	,@Vendor_ProjectFK int = null  
	,@Latitude float = null  
	,@Longitude float = null  
	,@CreatedDate datetime = null  
	,@SerialNumber varchar(25) = null  
	,@Comments ntext = null  
	,@ImageFileName nvarchar(250) = null  
as
	/* 	Initial Generated Date:  Sunday, June 13, 2010 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_Utility_Asset_Transformers 
	(
	
	 [UtilityFK] 
	,[Vendor_ProjectFK] 
	,[Latitude] 
	,[Longitude] 
	,[CreatedDate] 
	,[SerialNumber] 
	,[Comments] 
	,[ImageFileName] 
	)
	values
	(
	
	 @UtilityFK 
	,@Vendor_ProjectFK 
	,@Latitude 
	,@Longitude 
	,@CreatedDate 
	,@SerialNumber 
	,@Comments 
	,@ImageFileName 
	)
set @Utility_Asset_TransformerId = scope_identity()
select * from v_tb_Utility_Asset_Transformers where Utility_Asset_TransformerId = @Utility_Asset_TransformerId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetTransformer_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetTransformer_Update
GO


create proc up_UtilityAssetTransformer_Update
	 @Utility_Asset_TransformerId int  
	,@UtilityFK int = null  
	,@Vendor_ProjectFK int = null  
	,@Latitude float = null  
	,@Longitude float = null  
	,@CreatedDate datetime = null  
	,@SerialNumber varchar(25) = null  
	,@Comments ntext = null  
	,@ImageFileName nvarchar(250) = null  
	,@OriginalUtility_Asset_TransformerId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Sunday, June 13, 2010 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalUtility_Asset_TransformerId is null
	set @OriginalUtility_Asset_TransformerId = @Utility_Asset_TransformerId

-- test
declare @Result bit
execute @Result = up_UtilityAssetTransformer_Test @OriginalUtility_Asset_TransformerId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Utility_Asset_TransformerId)
	raiserror(505060, 16, 1, 'UtilityAssetTransformer', @x)
	return
end

-- proceed
update tb_Utility_Asset_Transformers 
set

	 [UtilityFK] = IsNull(@UtilityFK, [source].[UtilityFK])
	,[Vendor_ProjectFK] =  @Vendor_ProjectFK
	,[Latitude] =  @Latitude
	,[Longitude] =  @Longitude
	,[CreatedDate] =  @CreatedDate
	,[SerialNumber] =  @SerialNumber
	,[Comments] =  @Comments
	,[ImageFileName] =  @ImageFileName
	
from tb_Utility_Asset_Transformers source
where
	[source].[Utility_Asset_TransformerId] = @OriginalUtility_Asset_TransformerId


select * from v_tb_Utility_Asset_Transformers where Utility_Asset_TransformerId = @Utility_Asset_TransformerId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetTransformer_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetTransformer_Delete
GO


create proc up_UtilityAssetTransformer_Delete
	@Utility_Asset_TransformerId int
as
	/* 	Initial Generated Date:  Sunday, June 13, 2010 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_UtilityAssetTransformer_Test @Utility_Asset_TransformerId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Utility_Asset_TransformerId)
	raiserror(505060, 16, 1, 'UtilityAssetTransformer', @x)
	return
end

-- proceed
delete from tb_Utility_Asset_Transformers 
where [Utility_Asset_TransformerId] = @Utility_Asset_TransformerId

GO


